package com.caipiao.util.db;

/**
 * @author GaoFeng
 * @time 2014年1月13日
 * 
 */

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

import javax.sql.rowset.CachedRowSet;

import com.caipiao.lottery.dbwarpper.NamedParameterStatement;
import jodd.props.Props;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.druid.pool.DruidDataSource;
import com.sun.rowset.CachedRowSetImpl;
//import java.util.concurrent.locks.Lock;
//import java.util.concurrent.locks.ReentrantReadWriteLock;

//单态
public class DBHelper
{
    private static DBHelper instance = new DBHelper();				//数据库助手实例
    
    /*
    //锁
    private ReentrantReadWriteLock rwl = new ReentrantReadWriteLock();
  	private Lock readLock = rwl.readLock();
  	private Lock writeLock = rwl.writeLock();
  	*/
    
    private byte[] local_lock = new byte[0];
  	
  	//存储过程缓存表
  	//private HashMap<String, String> procMap = new HashMap<String, String>();
    
    //数据库 - 连接池
    public HashMap<String, DruidDataSource> dbPoolMap = 
    		new HashMap<String, DruidDataSource>();
   
    
    Logger db_logger = null;
    
    Logger db_query_logger = null;
    
    //缺省数据库连接池编号
    String defaultPoolId = "";
    
    //禁止外部构造
    private DBHelper()
    {      
    }
	
    //获取数据库助手实例
    public static final DBHelper Instance() 
    {
    	return instance;
    }

    public String Get_DB_Status()
    {
    	StringBuffer status = new StringBuffer();
    	
    	status.append("<dbstatus>\r\n");
    	try
    	{
    		Iterator<Entry<String, DruidDataSource>> it = this.dbPoolMap.entrySet().iterator();
    		while(it.hasNext())
    		{
    			Entry<String, DruidDataSource> entry = (Entry<String, DruidDataSource>) it.next();
    			
    			DruidDataSource dbPool = entry.getValue();
    			
    			status.append("<db>");
    			status.append("数据库:");
    			status.append(entry.getKey());
    			status.append(" - 可用连接:");
    			status.append(dbPool.getPoolingCount());
    			
    			status.append(" - 使用中:");
    			status.append(dbPool.getActiveCount());
    			status.append("</db>\r\n");
			}
    	}
    	catch (Exception e)
    	{
    		status.append("数据库状态信息异常");
    	}
    	
    	status.append("</dbstatus>\r\n");
    	
    	return status.toString();
    }
    
    //日志
    public Logger Get_DB_Loger()
    {
    	return this.db_logger;
    }
    
    //数据库初始化
    //输入数据库配置文件路径
    //多数据库配置
    public boolean initDB(Props dbProps)
    {
	  	try
    	{	
	  		db_logger = LoggerFactory.getLogger("DBCore");
        	db_query_logger = LoggerFactory.getLogger("DBQuery");
        	
        	db_logger.debug("开始初始化数据库连接池配置");
        	
        	String[] dbList = dbProps.getValue("DB").split(",");
        	
        	Properties prop = new Properties();
        	
        	for (int i=0; i<dbList.length;++i)
        	{
        		prop.clear();
        		dbProps.extractProps(prop, dbList[i]);
        		
        		if (!prop.getProperty("DB.Enable").equalsIgnoreCase("1"))
				{
					continue;
				}

        		String Id = prop.getProperty("DB.ID");
        		
				String default_flag = prop.getProperty("DB.Default", dbList[0]);
				if (default_flag != null && default_flag.equalsIgnoreCase("1"))
				{
					this.defaultPoolId = Id;
				}
        		
				/*
				ComboPooledDataSource dbPool = new ComboPooledDataSource();
				
				dbPool.setDriverClass(prop.getProperty("DB.DriverClass"));
				dbPool.setJdbcUrl(prop.getProperty("DB.JdbcUrl"));  
				dbPool.setUser(prop.getProperty("DB.User"));
				dbPool.setPassword(prop.getProperty("DB.Password"));
                
				dbPool.setInitialPoolSize(Integer.valueOf(prop.getProperty("DB.InitialPoolSize")));
				dbPool.setMaxPoolSize(Integer.valueOf(prop.getProperty("DB.MaxPoolSize")));
				dbPool.setMinPoolSize(Integer.valueOf(prop.getProperty("DB.MinPoolSize")));
				
				dbPool.setAcquireIncrement(Integer.valueOf(prop.getProperty("DB.IncrementSize")));
				dbPool.setAcquireRetryAttempts(Integer.valueOf(prop.getProperty("DB.RetryAttempts")));
				dbPool.setAcquireRetryDelay(Integer.valueOf(prop.getProperty("DB.RetryDelay")));
				
				dbPool.setIdleConnectionTestPeriod(Integer.valueOf(prop.getProperty("DB.IdleCheckTime")));
				dbPool.setMaxIdleTime(Integer.valueOf(prop.getProperty("DB.MaxIdleTime")));
				dbPool.setCheckoutTimeout(Integer.valueOf(prop.getProperty("DB.MaxWait")));
				
				dbPool.setNumHelperThreads(Integer.valueOf(prop.getProperty("DB.HelperThreads")));
				dbPool.setMaxStatements(Integer.valueOf(prop.getProperty("DB.MaxStatements")));
				dbPool.setMaxStatementsPerConnection(100);
				
				dbPool.setUnreturnedConnectionTimeout(60);
				
				*/
					
				db_logger.debug("数据库连接池初始化" + Id + "开始");

				DruidDataSource dbPool = new DruidDataSource();
				
				dbPool.setName(Id);
				dbPool.setDriverClassName(prop.getProperty("DB.DriverClass"));
				dbPool.setUrl(prop.getProperty("DB.JdbcUrl"));
				dbPool.setUsername(prop.getProperty("DB.User"));
				dbPool.setPassword(prop.getProperty("DB.Password"));
				
				dbPool.setInitialSize(Integer.valueOf(prop.getProperty("DB.InitialPoolSize")));
				dbPool.setMaxActive(Integer.valueOf(prop.getProperty("DB.MaxPoolSize")));
				dbPool.setMinIdle(Integer.valueOf(prop.getProperty("DB.MinPoolSize")));
				
				dbPool.setMaxWait(Integer.valueOf(prop.getProperty("DB.MaxWait")));
				dbPool.setTimeBetweenEvictionRunsMillis(Integer.valueOf(prop.getProperty("DB.IdleCheckTime")));
				dbPool.setMinEvictableIdleTimeMillis(Integer.valueOf(prop.getProperty("DB.MaxIdleTime")));
				
				dbPool.setMaxPoolPreparedStatementPerConnectionSize(Integer.valueOf(prop.getProperty("DB.MaxStatements")));
				
				db_logger.debug("数据库连接池初始化" + Id + "完成");
				
				//连接池加入MAP
				this.dbPoolMap.put(Id, dbPool);
			}
			
			db_logger.debug("数据库连接池初始化结束");
			
			return true;
    	}
    	catch (Exception e)
    	{
    		//初始化数据连接池参数异常
    		db_logger.error("数据库初始化异常:" + e.toString());
    	}
    	
    	return false;
    }
    
    //数据库连接测试
    public boolean DBLinkTest()
    {
		if (this.dbPoolMap.isEmpty())
		{
			return false;
		}
		
		Iterator<Entry<String, DruidDataSource>> it = this.dbPoolMap.entrySet().iterator();
		while(it.hasNext())
		{
			Entry<String, DruidDataSource> entry = (Entry<String, DruidDataSource>) it.next();
			
			Connection conn = null;
			try
			{
				String dbId = entry.getKey();
				try
				{
					conn = entry.getValue().getConnection();
					if (null == conn)
					{
						throw new Exception("连接对象为空");
					}

    				db_logger.debug("数据库" + dbId + "连接测试正常");
				} 
				catch (Exception e)
				{
					db_logger.error("测试数据库 " + dbId + " 异常:" + e.toString());
					return false;
				}
			}
			finally 
    		{
    			this.freeResource(conn, null, null);		//释放资源
    		}
		}

		return true;
    }
    
    //重置连接池
    public synchronized final void Reset() throws SQLException 
    {
    	synchronized(local_lock)
    	{
    		Iterator<Entry<String, DruidDataSource>> it = this.dbPoolMap.entrySet().iterator();
    		while(it.hasNext())
    		{
    			Entry<String, DruidDataSource> entry = (Entry<String, DruidDataSource>) it.next();
    			entry.getValue().restart();
    		}
    	}
    }
    
    //获取一个连接对象，指定连接池不存在时 返回缺省连接池连接
    public final Connection getConnection(String DbId, String dbName, Logger logger) 
    {
        try 
        {
        	DruidDataSource dbPool = null;
        	Connection resConn = null;
        	synchronized(local_lock)
        	{
	    		if (null != dbName && !dbName.isEmpty())
	    		{
	    			dbPool = this.dbPoolMap.get(dbName.toUpperCase());	//大小写兼容
	    		}
	
	    		//连接池为空 返回缺省连接池
	        	if (null == dbPool)
	        	{
	        		dbPool = this.dbPoolMap.get(this.defaultPoolId);
	        		
	        		if (null != dbPool)
	        		{
	        			logger.trace(DbId + " - 指定数据库" + dbName +"配置不存在,使用缺省数据库:" + this.defaultPoolId);
	        		}
	        		else
	        		{
	        			logger.error(DbId + " - 指定数据库" + dbName +"配置不存在,且未指定缺省数据库");
	        		}
	        	}
        	}

        	//连接池存在
    		if (null != dbPool) {
    			resConn = dbPool.getConnection();
    			if(null != resConn){
    				resConn.setAutoCommit(true);
    			}
    			return resConn;
    		}
        } 
        catch (SQLException e) 
        {
        	logger.error(DbId + " - DBHelper, getConnection(), 获取数据库连接异常, ", e);
        }
        return null;
    }

    //关闭数据库连接池
    protected void finalize() throws Throwable 
    { 
		Iterator<Entry<String, DruidDataSource>> it = this.dbPoolMap.entrySet().iterator();
		while(it.hasNext())
		{
			Entry<String, DruidDataSource> entry = (Entry<String, DruidDataSource>) it.next();
			entry.getValue().close();
		}
        super.finalize();
    }
    
    //释放数据库链接
    public void freeResource(Connection conn, Statement st, ResultSet rs)
    {
    	if (null != rs)
    	{
            try 
            {
                rs.close();
            } 
            catch (SQLException e) 
            {
               // 释放数据库连接RS异常 
            }
    	}
        
        if(st != null)
        {
            try 
            {
                st.close();
            } 
            catch (SQLException e) 
            {
                //释放数据库连接Stat异常
            }
        }
        
        if(conn != null)
        {
            try 
            {
//            	conn.commit();
                conn.close();
            } 
            catch (SQLException e) 
            {
               //释放数据库连接Conn异常   
            }
        }
    }
    
    //释放数据库链接
    public void closeResource(Connection conn, NamedParameterStatement ps, ResultSet rs)
    {
    	if (null != ps)
    	{
            try 
            {
                ps.close();
            } 
            catch (SQLException e) 
            {
               // 释放数据库连接RS异常 
            }
    	}
    	freeResource(conn, null, rs);
    }
    
    //DBLoader加载数据
    public boolean DbLoadDate(String dbId, DBLoader loader)
    {
    	if (null == loader)
    	{
    		return false;
    	}
    	
    	Connection conn = null;
		try
		{
			conn = this.getConnection(dbId, loader.GetDbName(), this.db_logger);		//连接池获取链接
			
			if (null == conn)
			{
				throw new Exception("获取数据库连接异常,:DBPoolName:" + loader.GetDbName());
			}
			
			return loader.LoadData(conn);
		}
		catch(Exception e)
		{			
			db_logger.error(dbId + " " + loader.LoaderName() + "执行异常:" + e.toString());
		}
		finally 
		{
			this.freeResource(conn, null, null);		//释放资源
		}
		
		return false;
    }
    
    //缺省使用日志
    public boolean excuteCoreProc(String DbId, String DbName, String procName, DBParam[] param)
    {
    	return this.excuteDBProc(DbId, DbName, procName, param, this.db_logger, true);
    }

    public boolean excuteCoreProc(String DbId, String DbName, String procName, DBParam[] param, boolean isLog)
    {
    	return this.excuteDBProc(DbId, DbName, procName, param, this.db_logger, isLog);
    }
    
    //缺省不使用日志
    public boolean excuteQueryProc(String DbId, String DbName, String procName, DBParam[] param)
    {
    	return this.excuteDBProc(DbId, DbName, procName, param, this.db_query_logger, false);
    }

    public boolean excuteQueryProc(String DbId, String DbName, String procName, DBParam[] param, boolean isLog)
    {
    	return this.excuteDBProc(DbId, DbName, procName, param, this.db_query_logger, isLog);
    }
    

    
    public boolean excuteDBProc(String DbId, String DbName, String procName, DBParam[] param, Logger logger, boolean isLog)
    {
		Connection conn = null;
		CallableStatement cs = null;
		
		try
		{
			conn = this.getConnection(DbId, DbName, logger);		//连接池获取链接
			if (null == conn)
			{
				throw new Exception("获取数据库连接异常,:DBPoolName" + DbName);
			}

			//构建存储过程执行函数字符串
			String pl_sql = this.getDBProcStr(procName, param);
			cs = conn.prepareCall(pl_sql);

			if (isLog)
			{
				logger.trace(DbId + " - 构建存储过程调用字符串:" + DbName + " - " + pl_sql);
			}

			//设置函数参数
			this.setDBProcParam(cs, param);
			
			if (isLog)
			{
				logger.debug(DbId + " - 存储过程调用输入参数设置成功" + DbName + " - "  + procName + DBParam.Get_Input_Param_Str(param));
			}

			long exetime = System.currentTimeMillis();
			
			//执行存储过程
			cs.execute();
			
			exetime = System.currentTimeMillis()-exetime;
			
			if (isLog)
			{
				logger.info(DbId + " - 数据库存储过程调用成功" + DbName + " - "  + procName+ " [执行时间：" + exetime + " ms]");
			}
			
			if (exetime > 1000)
			{
				logger.warn(DbId + " - 数据库存储过程执行慢:" + DbName + " - "  + procName+ " [执行时间：" + exetime + " ms], 参数:" + DBParam.Get_Input_Param_Str(param));
			}

			//生成返回值
			this.makeDBProcResult(cs, param);
			
			if (isLog)
			{
				//记录数据库输出参数
				logger.debug(DbId + " - 数据库存储过程 返回参数解析成功" + DbName + " - "  + procName + DBParam.Get_Output_Param_Str(param));
			}

			return true;
			
		}
		catch(SQLException e)
		{
			db_logger.error(DbId + ":" + DbName + " - "  + procName + "执行异常:" + e.toString() + "DBParam:"  + DBParam.Get_Input_Param_Str(param));
		}
		catch(Exception e)
		{			
			db_logger.error(DbId + ":"+ DbName + " - "  + procName + "执行异常:" + e.toString() + "DBParam:"  + DBParam.Get_Input_Param_Str(param));
		}
		finally 
		{
			this.freeResource(conn, cs, null);		//释放资源
		}
		return false;
    }
    
	//生成数据库执行函数字符串
	public String getDBProcStr(String procName, DBParam[] param)
	{
		/*
		String key = procName + "$" + String.valueOf(param.length);
		
		String sqlStr = "";
		try
		{
			this.readLock.lock();
			
			sqlStr = this.procMap.get(key);
		}
		finally
		{
			this.readLock.unlock();
		}

		if (null == sqlStr || sqlStr.isEmpty())
		{
			sqlStr = this.makeDBProcStr(procName, param);
			
			try
			{
				this.writeLock.lock();
				this.procMap.put(key, sqlStr);
			}
			finally
			{
				this.writeLock.unlock();
			}
		}

		return sqlStr;*/
		
		return this.makeDBProcStr(procName, param);
	}
	
	private String makeDBProcStr(String procName, DBParam[] param)
	{
		StringBuffer pl_sql = new StringBuffer();

		pl_sql.append("{call ");
		pl_sql.append(procName);
		pl_sql.append("(");
		
		boolean first = true;
		
		for(int i = 0; i < param.length; i++)
		{			
			switch(param[i].paramType)
			{
				case pInput://输入参数
				case pOutput://输出参数
				{
					if (first)
					{
						first = false;
					}
					else
					{
						pl_sql.append(",");
					}
				
					pl_sql.append("?");
				
					break;
				}
				default:
					break;
			}
		}
		
		pl_sql.append(")}");
		
		return pl_sql.toString();
	}
	
	
	//设置存储过程参数
	private void setDBProcParam(CallableStatement cs, DBParam[] param) throws SQLException
	{
		int k = 0;
		for(int i = 0; i < param.length; i++)
		{
			switch(param[i].paramType)
			{
				case pInput://输入参数
				{
					if(null == param[i].object)
					{
						cs.setString(++k, "");
					}
					else
					{
						cs.setString(++k, (String)param[i].object);
					}
					break;
				}
				case pOutput://输出参数
				{
					switch(param[i].fieldType)	//域类型
					{
						case pString:			//字符串
						{
							cs.registerOutParameter(++k, java.sql.Types.VARCHAR);
							break;
						}
//						case pCursor:		//数据集
//						{
//							cs.registerOutParameter(++k, java.sql.Types.);
//							break;
//						}
						default:
							break;
					}
					break;
				}
				default:
					break;
			}
		}
	}
	
	
	//数据库存储过程输出结果解析
		@SuppressWarnings("unused")
		private void makeDBSqlResult(CallableStatement cs, DBParam[] param) 
				throws SQLException
		{
			int k = 0;
			for(int i = 0; i < param.length; i++)
			{
				switch(param[i].paramType)
				{
					case pInput://输入参数
					{
						++k;
						break;
					}
					case pOutput://输出参数
					{
						try
						{
//							++k;
							
							if (null == param[i].object)	//不需要返回
							{
								break;
							}
							
							Object obj = cs.getObject(k);
//	    					if (null == obj)	//数据库空对象
//	    					{
//	    						break;
//	    					}
	    	
	    					switch(param[i].fieldType)	//域类型
	    					{
	    						case pString:			//字符串
	    						{	
	    							((DBOutString)param[i].object).setString(obj.toString());	//字符串
	    							break;
	    						}
	    						case pCursor:		//数据集
	    						{
	    							ResultSet rs = (ResultSet)obj;
									CachedRowSet cachedRS = new CachedRowSetImpl();
	    							cachedRS.populate(rs);
	    							
	    							this.freeResource(null, null, rs);		//释放资源
	    							
	    							((DBOutCursor)param[i].object).setResultSet(cachedRS);	//离线数据集
	    							break;
	    						}
	    						default:
	    							break;
	    					}
						}
						catch(SQLException e)
						{
							//没有对象
							e.printStackTrace();
						}
						catch(Exception e)
						{
							//类型转换错误
						}
						break;
					}
					default:
						break;
				}
			}
		}

	
	
	//数据库存储过程输出结果解析
	private void makeDBProcResult(CallableStatement cs, DBParam[] param) throws SQLException
	{
		int k = 0;
		for(int i = 0; i < param.length; i++)
		{
			switch(param[i].paramType)
			{
				case pInput://输入参数
				{
					++k;
					break;
				}
				case pOutput://输出参数
				{
					try
					{
						++k;
						
						if (null == param[i].object)	//不需要返回
						{
							break;
						}
						
						Object obj = cs.getObject(k);
    					if (null == obj)	//数据库空对象
    					{
    						break;
    					}
    	
    					switch(param[i].fieldType)	//域类型
    					{
    						case pString:			//字符串
    						{	
    							((DBOutString)param[i].object).setString(obj.toString());	//字符串
    							break;
    						}
    						case pCursor:		//数据集
    						{
    							ResultSet rs = (ResultSet)obj;
								CachedRowSet cachedRS = new CachedRowSetImpl();
    							cachedRS.populate(rs);
    							
    							this.freeResource(null, null, rs);		//释放资源
    							
    							((DBOutCursor)param[i].object).setResultSet(cachedRS);	//离线数据集
    							break;
    						}
    						default:
    							break;
    					}
					}
					catch(SQLException e)
					{
						//没有对象
						e.printStackTrace();
					}
					catch(Exception e)
					{
						//类型转换错误
					}
					break;
				}
				default:
					break;
			}
		}
	}
}
